﻿using System;
using System.Collections.Generic;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Ry.Defined;
using Ry.Error;

namespace Ry.Database
{
    public class CRyORACLE:DBInterface
    {
        private const string Version = "6.0";
        public OracleConnection ORAconn;
        public OracleCommand ORAcmd;
        public OracleDataAdapter ORAadp;
        public Error.RyError ryError;

        public CRyORACLE()
        {
            ryError = new Error.RyError();
        }

        public EnumDatabaseType DatabaseType
        {
            get { return EnumDatabaseType.ORACLE; }
            set { }
        }

        public string DBVersion
        {
            get { return Version; }
            set { }
        }
        public string DataSource { get; set; }
        public int Port { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public string DBName { get; set; }
        public bool DBConnected
        {
            get { return ORAconn.State == ConnectionState.Open; }
            set { ConnectDB(); }
        }
        public bool KeepConnect { get; set; }

  
        public int ExecuteProgram(string programName, Dictionary<string, object> dictParam, string outParaName, ref DataTable dt)
        {
            ORAcmd.CommandText = programName;
            ORAcmd.CommandType = CommandType.StoredProcedure;
            ORAcmd.Parameters.Clear();
            OracleParameter p1;
            if (dictParam != null)
            foreach (KeyValuePair<string, object> KeyValue in dictParam)
            {
                p1 = new OracleParameter(KeyValue.Key, OracleDbType.Varchar2);
                p1.Value = KeyValue.Value;
                p1.Direction = ParameterDirection.Input;
                ORAcmd.Parameters.Add(p1);
            }

            OracleParameter p2 = new OracleParameter("outParaName", OracleDbType.RefCursor);
            p2.Direction = ParameterDirection.Output;
            ORAcmd.Parameters.Add(p2);
            try
            {
                ORAadp.Fill(dt);
                return MyConst.Success;
            }
            catch (Exception e)
            {
                ryError.ShowError(e, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int ExecuteProgram(string programName, Dictionary<string, object> dictParam, out string result)
        {
            result = "";
            ORAcmd.CommandText = programName;
            ORAcmd.CommandType = CommandType.StoredProcedure;
            ORAcmd.Parameters.Clear();
            OracleParameter p1;
            if (dictParam != null)
            foreach (KeyValuePair<string, object> KeyValue in dictParam)
            {
                p1 = new OracleParameter(KeyValue.Key, OracleDbType.Varchar2);
                p1.Value = KeyValue.Value;
                p1.Direction = ParameterDirection.Input;
                ORAcmd.Parameters.Add(p1);
            }

            OracleParameter p2 = new OracleParameter("ResultStr", OracleDbType.Varchar2, 100);
            p2.Direction = ParameterDirection.Output;
            ORAcmd.Parameters.Add(p2);
            try
            {
                ORAcmd.ExecuteNonQuery();
                result = ORAcmd.Parameters["ResultStr"].Value.ToString();
                return MyConst.Success;
            }
            catch (Exception e)
            {
                ryError.ShowError(e, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int GetDataSet(string sqlString, CommandType commandType, Dictionary<string, object> dictParam, ref DataSet ds)
        {
            DataTable dt = new DataTable();
            if (GetDataSet(sqlString, commandType, dictParam, ref dt) == MyConst.Failed) return MyConst.Failed;
            DataSet ds1 = new DataSet();
            ds.Tables.Add(dt);
            return MyConst.Success;
        }

        public int GetDataSet(string sqlString, CommandType commandType, Dictionary<string, object> dictParam, ref DataTable ds)
        {
            DataSet dt = new DataSet();
            try
            {

                ORAcmd.CommandText = sqlString;
                ORAcmd.CommandType = commandType;

                ORAcmd.Parameters.Clear();
                if (dictParam != null)
                foreach (KeyValuePair<string, object> KeyValue in dictParam)
                {
                    //OracleParameter p1 = new OracleParameter(KeyValue.Key, OracleType.VarChar);
                    //p1.Value = KeyValue.Value;
                    //p1.Direction = ParameterDirection.Input;
                    //this._command.Parameters.Add(p1);
                    ORAcmd.Parameters.Add(KeyValue.Key, KeyValue.Value);
                }

                // OracleParameter O = new OracleParameter("result", OracleDbType.RefCursor,ParameterDirection.Output);

                // O.Direction = ParameterDirection.Output;
                // ORAcmd.Parameters.Add(O);

                ORAadp.Fill(ds);
                return MyConst.Success;
            }
            catch (Exception e)
            {
                ryError.ShowError(e, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int ExecuteNonQuery(string sqlString, CommandType commandType, Dictionary<string, object> dictParam)
        {
            try
            {

                ORAcmd.CommandText = sqlString;
                ORAcmd.CommandType = commandType;

                ORAcmd.Parameters.Clear();
                if (dictParam != null)
                foreach (KeyValuePair<string, object> KeyValue in dictParam)
                {
                    ORAcmd.Parameters.Add(KeyValue.Key, KeyValue.Value);
                }

                ORAcmd.ExecuteNonQuery();
                return MyConst.Success;
            }
            catch (Exception e)
            {
                ryError.ShowError(e, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int DelValues(string tableName, string fieldName, string fieldValue)
        {
            try
            {

                ORAcmd.CommandText = string.Format("delete {0} where {1} = {2})", tableName, fieldName, fieldValue);
                ORAcmd.CommandType = CommandType.Text;
                ORAcmd.Parameters.Clear();
                ORAcmd.ExecuteNonQuery();
                return MyConst.Success;
            }
            catch (Exception e)
            {
                ryError.ShowError(e, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int UpdataOneValues(string tableName, string updateField, string fieldValues, string conditionName,
            string conditionValues)
        {
            try
            {

                ORAcmd.CommandText = string.Format("update {0} set  {1} = {2} where  {3} = {4})", tableName, updateField, fieldValues, conditionName, conditionValues);
                ORAcmd.CommandType = CommandType.Text;
                ORAcmd.Parameters.Clear();
                ORAcmd.ExecuteNonQuery();
                return MyConst.Success;
            }
            catch (Exception e)
            {
                ryError.ShowError(e, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int ConnectDB(string dbSource, int port, string username, string passwd, string dbName, bool immediateConn)
        {
            string connString="";
            DataSource = dbSource;
            UserName = username;
            Password = passwd;
            DBName = dbName;
            Port = port;
            try
            {
                    if (ORAconn == null)
                    {
                        connString = string.Format(
                            "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})" +
                            "(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));" +
                            "Persist Security Info=True;User ID={3};Password={4};", DataSource, Port, DBName, UserName, Password);
                        ORAconn = new OracleConnection(connString);
                        if (immediateConn == true) ORAconn.Open();
                        ORAcmd = new OracleCommand("", ORAconn);
                        ORAadp = new OracleDataAdapter(ORAcmd);
                    }
                return MyConst.Success;
            }
            catch (Exception ex)
            {
                System.Diagnostics.StackFrame v = new System.Diagnostics.StackFrame(true);
                ryError.ShowError(ex, connString,v);
                return MyConst.Failed;
            }
        }

        public int ConnectDB()
        {
            string connString = "";
            try
            {
                if (ORAconn == null)
                {
                        connString = string.Format(
                            "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})" +
                            "(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));" +
                            "Persist Security Info=True;User ID={3};Password={4};", DataSource, Port, DBName, UserName, Password);
                        ORAconn = new OracleConnection(connString);
                        ORAconn.Open();
                        ORAcmd = new OracleCommand("", ORAconn);
                        ORAadp = new OracleDataAdapter(ORAcmd);
                 }

                ORAconn.Open();
                return MyConst.Success;
            }
            catch (Exception ex)
            {
                ryError.ShowError(ex, connString, new System.Diagnostics.StackFrame(true));
                return MyConst.Failed;
            }
        }

        public int Disconnect()
        {
            ORAconn.Close();
            return MyConst.Failed;
        }

        public void Dispose()
        {
             ORAconn.Dispose();
             ORAcmd.Dispose();
             ORAadp.Dispose();
             ORAconn = null;
             ORAcmd = null;
             ORAadp = null;
        }
    }
}
